package com.example.dao;



import com.example.entity.Student;

import com.example.utils.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentDao {
    // 在现有的StudentDao类中添加以下方法

    public Student getStudentById(int id) {
        String sql = "SELECT * FROM student WHERE id = ?";
        Student student = null;

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, id);
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                student = new Student();
                student.setId(rs.getInt("id"));
                student.setStudentId(rs.getString("student_id"));
                student.setName(rs.getString("name"));
                student.setGrade(rs.getString("grade"));
                student.setClasses(rs.getString("classes"));
                student.setEmail(rs.getString("email"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return student;
    }
    //更新学生
    public boolean updateStudent(Student student) {
        String sql = "UPDATE student SET student_id=?, name=?, grade=?, classes=?, email=? WHERE id=?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, student.getStudentId());
            pstmt.setString(2, student.getName());
            pstmt.setString(3, student.getGrade());
            pstmt.setString(4, student.getClasses());
            pstmt.setString(5, student.getEmail());
            pstmt.setInt(6, student.getId());

            int result = pstmt.executeUpdate();
            return result > 0;

        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }
    
    // 添加学生
    public boolean addStudent(Student student) {
        String sql = "INSERT INTO student (student_id, name, grade, classes, email) VALUES (?, ?, ?, ?, ?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, student.getStudentId());
            pstmt.setString(2, student.getName());
            pstmt.setString(3, student.getGrade());
            pstmt.setString(4, student.getClasses());
            pstmt.setString(5, student.getEmail());
            
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    //根据id删除学生
    public boolean deleteStudentById(int id) {
        String sql = "DELETE FROM student WHERE id = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, id);
            int result = pstmt.executeUpdate();

            return result > 0;

        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }
//    public boolean deleteStudent(int id){
//        String sql = "delete from student where id = ?";
//        try(Connection conn = DBUtil.getConnection();
//            PreparedStatement pstmt = conn.prepareStatement(sql)) {
//            pstmt.setInt(1,id);
//            int i = pstmt.executeUpdate();
//
//            if (i==1) {
//                return true;
//            }
//        } catch (SQLException throwables) {
//            throwables.printStackTrace();
//        }
//        return false;
//    }
    
    // 通过用户名和邮箱验证
    public Student validateStudent(String username, String email) {
        String sql = "SELECT * FROM student WHERE name = ? AND email = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, username);
            pstmt.setString(2, email);
            ResultSet rs = pstmt.executeQuery();
            
            if (rs.next()) {
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setStudentId(rs.getString("student_id"));
                student.setName(rs.getString("name"));
                student.setGrade(rs.getString("grade"));
                student.setClasses(rs.getString("classes"));
                student.setEmail(rs.getString("email"));
                return student;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    
    // 检查学号是否已存在
    public boolean isStudentIdExists(String studentId) {
        String sql = "SELECT COUNT(*) FROM student WHERE student_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, studentId);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
    // 检查邮箱是否已存在
    public boolean isEmailExists(String email) {
        String sql = "SELECT COUNT(*) FROM student WHERE email = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, email);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }



    public List<Student> searchStudents(String keyword) {
        List<Student> result = new ArrayList<>();
        String sql = "SELECT * FROM student WHERE " +
                "student_id LIKE ? OR " +
                "name LIKE ? OR " +
                "grade LIKE ? OR " +
                "classes LIKE ? OR " +
                "email LIKE ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            String searchPattern = "%" + keyword + "%";
            for (int i = 1; i <= 5; i++) {
                pstmt.setString(i, searchPattern);
            }

            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setStudentId(rs.getString("student_id"));
                student.setName(rs.getString("name"));
                student.setGrade(rs.getString("grade"));
                student.setClasses(rs.getString("classes"));
                student.setEmail(rs.getString("email"));
                result.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }



    public static boolean update(Student student) throws SQLException {
        Statement st=null;
        Connection conn=null;
        try {
            conn = DBUtil.getConnection();
            st = conn.createStatement();
            String sql="update student set student_id='"+student.getStudentId()+"', name='"+student.getName()+"'," +
                    "grade='"+student.getGrade()+"'," +
                    " classes='"+student.getClasses()+"',"+
                    "email='"+student.getEmail()+"' where id="+student.getId();
            int i = st.executeUpdate(sql);
            if(i>0)return true;
            else return false;

        } catch (SQLException e) {
            e.printStackTrace();

        }
        return false;

    }
}
